from sqlalchemy import *

class tablesDatabase:


    def __init__(self, location):
        #self.metadata = MetaData()
        #Session = sessionmaker();
        self.engine = create_engine("sqlite:///" + location)
        self.connection = self.engine.connect()
        #Session.configure(bind=engine)
        #self.session = Session()
        #self.connection = sqlite3.connect(location)
        #self.run = self.connection.cursor()

    def execute(self, sql):
        self.run.execute(sql)

    def runSQL(self, table, sql):
        self.run.execute(sql)
        return self.run.fetchall()

    def fetchone(self):
        return self.run.fetchone()

    def fetchall(self):
        return self.run.fetchall()
        
    def close(self):
        self.connection.close()

    def getDatabase(self):
        self.run.execute("select name from sqlite_master where type = 'table';")
        tables = []
        for table in self.run.fetchall():
            tables.append(table[0])
        return tables

    def getTable(self, table):
        columns = self.getTableColumns(table)
        self.run.execute("SELECT * FROM " + table)
        data = {'items':[]}
        for row in self.run.fetchall():
            rowCol = {}
            number = 0
            for col in row:
                rowCol[columns[number]] = col
                number = number + 1
            data['items'].append(rowCol)
        return data

    def getTableColumns(self, table, number=False):
        tableColumns = []
        self.run.execute("select sql from sqlite_master where type = 'table' and name = '{0}';".format(table))
        columns = self.run.fetchone()[0]
        columns = columns.split("(")[1]
        columns = columns.split(")")[0]
        columns = columns.split(",")
        for col in columns:
            col = col.lstrip()
            col = col.rstrip()
            col = col.split(" ")
            if number == True and (col[1] == "INTEGER" or col[1] == "REAL"):
                tableColumns.append(col[0])
            else:
                tableColumns.append(col[0])
        return tableColumns

    def getColumn(self, table, column):
        self.run.execute("SELECT {0} FROM {1}".format(column, table))
        rows = []
        for row in self.run.fetchall():
            rows.append(row[0])
        return rows

    def importTable(self, table, columns, data):
        sql = "CREATE TABLE " + table + " ("
        for column in columns:
            print column
            sql = "{0} {1} {2},".format(sql, column[0], column[1])
        sql = sql[:-1] + " )"
        print sql
        self.run.execute(sql)
        for row in data:
            rowcol = ""
            for col in row:
                if type(col) == unicode or type(col) == str:
                    rowcol = rowcol + "\"" + col + "\", "
                elif type(col) == int:
                    rowcol = rowcol + "" + col + ", "
            print rowcol[:-2]
            print ("INSERT INTO " + table + " VALUES ( " + rowcol[:-2] + " );")
            self.run.execute("INSERT INTO " + table + " VALUES ( " + rowcol[:-2] + " );")
        self.connection.commit()

    def destroyTable(self, table):
        self.run.execute("DROP TABLE {0};".format(table))
        self.connection.commit()
        
